"""
报表模块 - 业务服务层
"""

from io import BytesIO

from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill

from app.crud import report_crud
from app.schemas.report_schemas import (
	DeviceRunReportQuery,
	DeviceRunReportResponse,
	DeviceRunReportItem,
	AlarmWorkReportQuery,
	AlarmWorkReportResponse,
	AlarmWorkReportItem,
)
from common.database import DatabaseService
from common.exceptions import BizException, ErrorCode
from common.logger import create_logger
from common.response import PaginationParams

logger = create_logger(__name__)

# 状态映射字典
DEVICE_STATUS_MAP = {
	'ONLINE': '在线',
	'OFFLINE': '离线',
	'ABNORMAL': '异常',
	'DECOMMISSIONED': '已停用',
}

PHASE_MAP = {
	'A': 'A相',
	'B': 'B相',
	'C': 'C相',
}

ALARM_STATUS_MAP = {
	'ACTIVE': '活动',
	'CLEARED': '已清除',
	'ACKNOWLEDGED': '已确认',
}

PRIORITY_MAP = {
	'LOW': '低',
	'MEDIUM': '中',
	'HIGH': '高',
	'URGENT': '紧急',
}


async def get_device_run_report(
		db: DatabaseService,
		query: DeviceRunReportQuery,
		page: PaginationParams,
) -> DeviceRunReportResponse:
	"""
	获取设备运行报表

	Args:
		db: 数据库服务
		query: 查询参数
		page: 分页参数

	Returns:
		设备运行报表响应
	"""
	try:
		total, rows = await report_crud.get_device_run_report(
			db,
			query.keyword,
			query.start_date,
			query.end_date,
			query.status,
			query.manager_code,
			page.page,
			page.size,
		)
	except Exception as e:
		logger.exception(f"查询设备运行报表异常: {e}")
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR)

	# 转换数据并映射状态
	items = []
	for row in rows:
		item = DeviceRunReportItem(
			station_name=row.get('station_name'),
			box_serial_no=row.get('box_serial_no'),
			rtu_serial_no=row.get('rtu_serial_no'),
			rtu_status=DEVICE_STATUS_MAP.get(row.get('rtu_status'), row.get('rtu_status')),
			meter_serial_no=row.get('meter_serial_no'),
			meter_phase=PHASE_MAP.get(row.get('meter_phase'), row.get('meter_phase')),
			meter_status=DEVICE_STATUS_MAP.get(row.get('meter_status'), row.get('meter_status')),
			yesterday_kwh=float(row.get('yesterday_kwh', 0)),
			total_kwh=float(row.get('total_kwh', 0)),
			manager_name=row.get('manager_name'),
			address=row.get('address'),
		)
		items.append(item)

	# 计算分页信息
	pages = (total + page.size - 1) // page.size

	return DeviceRunReportResponse(
		total=total,
		items=items,
		page=page.page,
		size=page.size,
		pages=pages,
		has_next=page.page * page.size < total,
		has_prev=page.page > 1,
	)


async def export_device_run_report(
		db: DatabaseService,
		query: DeviceRunReportQuery,
) -> BytesIO:
	"""
	导出设备运行报表到Excel

	Args:
		db: 数据库服务
		query: 查询参数

	Returns:
		Excel文件的字节流
	"""
	try:
		# 查询全部数据（不分页）
		total, rows = await report_crud.get_device_run_report(
			db,
			query.keyword,
			query.start_date,
			query.end_date,
			query.status,
			query.manager_code,
			1,
			999999,  # 导出全部数据
		)
	except Exception as e:
		logger.exception(f"查询设备运行报表导出数据异常: {e}")
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR)

	# 创建Excel工作簿
	wb = Workbook()
	ws = wb.active
	ws.title = "设备运行报表"

	# 设置表头样式
	header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
	header_font = Font(color="FFFFFF", bold=True, size=11)
	header_alignment = Alignment(horizontal="center", vertical="center")

	# 定义表头
	headers = [
		"台区名称", "电箱序列号", "RTU序列号", "RTU状态",
		"电表序列号", "相别", "电表状态", "昨日用电量(kWh)", "累计电量(kWh)", "负责人姓名", "地址"
	]

	# 写入表头
	for col_idx, header in enumerate(headers, start=1):
		cell = ws.cell(row=1, column=col_idx, value=header)
		cell.fill = header_fill
		cell.font = header_font
		cell.alignment = header_alignment

	# 写入数据
	for row_idx, row in enumerate(rows, start=2):
		ws.cell(row=row_idx, column=1, value=row.get('station_name') or '')
		ws.cell(row=row_idx, column=2, value=row.get('box_serial_no') or '')
		ws.cell(row=row_idx, column=3, value=row.get('rtu_serial_no') or '')
		ws.cell(row=row_idx, column=4, value=DEVICE_STATUS_MAP.get(row.get('rtu_status'), row.get('rtu_status') or ''))
		ws.cell(row=row_idx, column=5, value=row.get('meter_serial_no') or '')
		ws.cell(row=row_idx, column=6, value=PHASE_MAP.get(row.get('meter_phase'), row.get('meter_phase') or ''))
		ws.cell(row=row_idx, column=7,
				value=DEVICE_STATUS_MAP.get(row.get('meter_status'), row.get('meter_status') or ''))
		ws.cell(row=row_idx, column=8, value=float(row.get('yesterday_kwh', 0)))
		ws.cell(row=row_idx, column=9, value=float(row.get('total_kwh', 0)))
		ws.cell(row=row_idx, column=10, value=row.get('manager_name') or '')
		ws.cell(row=row_idx, column=11, value=row.get('address') or '')

	# 调整列宽
	column_widths = [20, 18, 18, 12, 18, 10, 12, 18, 18, 15, 25]
	for col_idx, width in enumerate(column_widths, start=1):
		ws.column_dimensions[chr(64 + col_idx)].width = width

	# 保存到内存
	output = BytesIO()
	wb.save(output)
	output.seek(0)

	logger.info(f"设备运行报表导出完成，共 {total} 条记录")
	return output


async def get_alarm_work_report(
		db: DatabaseService,
		query: AlarmWorkReportQuery,
		page: PaginationParams,
) -> AlarmWorkReportResponse:
	"""
	获取告警工单报表

	Args:
		db: 数据库服务
		query: 查询参数
		page: 分页参数

	Returns:
		告警工单报表响应
	"""
	try:
		total, rows = await report_crud.get_alarm_work_report(
			db,
			query.keyword,
			query.start_date,
			query.end_date,
			query.alarm_status,
			query.priority,
			page.page,
			page.size,
		)
	except Exception as e:
		logger.exception(f"查询告警工单报表异常: {e}")
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR)

	# 转换数据（alarm_type和alarm_status已在SQL中转换为中文）
	items = []
	for row in rows:
		item = AlarmWorkReportItem(
			device_code=row.get('device_code'),
			alarm_type=row.get('alarm_type'),
			work_title=row.get('work_title'),
			alarm_status=row.get('alarm_status'),
			priority=PRIORITY_MAP.get(row.get('priority'), row.get('priority')),
			raised_at=row.get('raised_at'),
			recovered_at=row.get('recovered_at'),
			work_code=row.get('work_code'),
			assignee_name=row.get('assignee_name'),
			description=row.get('description'),
			address=row.get('address'),
		)
		items.append(item)

	# 计算分页信息
	pages = (total + page.size - 1) // page.size

	return AlarmWorkReportResponse(
		total=total,
		items=items,
		page=page.page,
		size=page.size,
		pages=pages,
		has_next=page.page * page.size < total,
		has_prev=page.page > 1,
	)


async def export_alarm_work_report(
		db: DatabaseService,
		query: AlarmWorkReportQuery,
) -> BytesIO:
	"""
	导出告警工单报表到Excel

	Args:
		db: 数据库服务
		query: 查询参数

	Returns:
		Excel文件的字节流
	"""
	try:
		# 查询全部数据（不分页）
		total, rows = await report_crud.get_alarm_work_report(
			db,
			query.keyword,
			query.start_date,
			query.end_date,
			query.alarm_status,
			query.priority,
			1,
			999999,  # 导出全部数据
		)
	except Exception as e:
		logger.exception(f"查询告警工单报表导出数据异常: {e}")
		raise BizException.from_error_code(ErrorCode.DB_QUERY_ERROR)

	# 创建Excel工作簿
	wb = Workbook()
	ws = wb.active
	ws.title = "告警工单报表"

	# 设置表头样式
	header_fill = PatternFill(start_color="4472C4", end_color="4472C4", fill_type="solid")
	header_font = Font(color="FFFFFF", bold=True, size=11)
	header_alignment = Alignment(horizontal="center", vertical="center")

	# 定义表头
	headers = [
		"设备编码", "告警类型", "工单标题", "告警状态",
		"优先级", "告警时间", "恢复时间", "工单编号", "负责人姓名", "工单描述", "地址"
	]

	# 写入表头
	for col_idx, header in enumerate(headers, start=1):
		cell = ws.cell(row=1, column=col_idx, value=header)
		cell.fill = header_fill
		cell.font = header_font
		cell.alignment = header_alignment

	# 写入数据
	for row_idx, row in enumerate(rows, start=2):
		ws.cell(row=row_idx, column=1, value=row.get('device_code') or '')
		ws.cell(row=row_idx, column=2, value=row.get('alarm_type') or '')
		ws.cell(row=row_idx, column=3, value=row.get('work_title') or '')
		ws.cell(row=row_idx, column=4, value=row.get('alarm_status') or '')
		ws.cell(row=row_idx, column=5, value=PRIORITY_MAP.get(row.get('priority'), row.get('priority') or ''))

		# 时间格式化
		raised_at = row.get('raised_at')
		ws.cell(row=row_idx, column=6, value=raised_at.strftime('%Y-%m-%d %H:%M:%S') if raised_at else '')

		recovered_at = row.get('recovered_at')
		ws.cell(row=row_idx, column=7, value=recovered_at.strftime('%Y-%m-%d %H:%M:%S') if recovered_at else '')

		ws.cell(row=row_idx, column=8, value=row.get('work_code') or '')
		ws.cell(row=row_idx, column=9, value=row.get('assignee_name') or '')
		ws.cell(row=row_idx, column=10, value=row.get('description') or '')
		ws.cell(row=row_idx, column=11, value=row.get('address') or '')

	# 调整列宽
	column_widths = [18, 15, 30, 12, 10, 20, 20, 18, 15, 40, 25]
	for col_idx, width in enumerate(column_widths, start=1):
		ws.column_dimensions[chr(64 + col_idx)].width = width

	# 保存到内存
	output = BytesIO()
	wb.save(output)
	output.seek(0)

	logger.info(f"告警工单报表导出完成，共 {total} 条记录")
	return output
